package com.jixiantubu.entity;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.gemptc.bean.TupianBean;
import com.gemptc.utils.DBUtil;
import com.gemptc.utils.DataUtil;
import com.jixiantubu.factory.TupianDao;


public class TupianDaoimp implements TupianDao {
	Connection con=DBUtil.getConnection();
	PreparedStatement pre=null;
	ResultSet res=null;
	@Override
	public List<TupianBean> selectTravelsPhoto(int pageNow) {
		List<TupianBean>list =new ArrayList<TupianBean>();
		int m = (pageNow - 1) * DataUtil.pageNum1;
		int n = DataUtil.pageNum1;
		String sql ="SELECT travels.`t_id`,travels.`t_title`,"
				+ "travels.`t_time`, photo_library.`pl_path`"
				+ " FROM photo_library,travels WHERE "
				+ "photo_library.`t_id`=travels.`t_id`"
				+ "ORDER BY t_time DESC limit ?,? ";
		try {
			pre=con.prepareStatement(sql);
			pre.setInt(1, m);
			pre.setInt(2, n);
			res=pre.executeQuery();
			while (res.next()) {
				int tid=res.getInt(1);
				String title=res.getString(2);
				String time=res.getString(3);
				String pl_path=res.getString(4);
				TupianBean tuBean=new TupianBean(tid, title, time, pl_path);
				list.add(tuBean);				
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			DBUtil.release(res, pre, con);
		}
		return list;
	}

	@Override
	public List<TupianBean> selectPostPhoto(int pageNow) {
		List<TupianBean>list =new ArrayList<TupianBean>();
		int m = (pageNow - 1) * DataUtil.pageNum1;
		int n = DataUtil.pageNum1;
		String sql ="SELECT  photo.`ph_id`, post_info.`pi_title`,"
				+ " photo.`ph_date`, photo_library.`pl_path` "
				+ "`photo_library`FROM photo_library,"
				+ "post_info,photo WHERE "
				+ "photo_library.`ph_id` = photo.`ph_id`"
				+ " AND photo.`pi_TopicID` = post_info.`pi_TopicID`"
				+ "ORDER BY ph_date DESC limit ?,? ;";
		try {
			pre=con.prepareStatement(sql);
			pre.setInt(1, m);
			pre.setInt(2, n);
			res=pre.executeQuery();
			while (res.next()) {
				int phid=res.getInt(1);
				String title=res.getString(2);
				String time=res.getString(3);
				String pl_path=res.getString(4);
				TupianBean tuBean=new TupianBean(phid, title, time, pl_path);
				list.add(tuBean);	
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			DBUtil.release(res, pre, con);
		}
		return list;
	}

	@Override
	public List<TupianBean> selectSciencePhoto(int pageNow) {
		List<TupianBean>list =new ArrayList<TupianBean>();
		int m = (pageNow - 1) * DataUtil.pageNum1;
		int n = DataUtil.pageNum1;
		String sql ="SELECT science.`s_id`,science.`s_title`,"
				+ "science.`s_upload`, science.`s_photo_path` "
				+ "FROM science ORDER BY s_upload DESC limit ?,? ;";
		try {
			pre=con.prepareStatement(sql);
			pre.setInt(1, m);
			pre.setInt(2, n);
			res=pre.executeQuery();
			while (res.next()) {
				int sid=res.getInt(1);
				String title=res.getString(2);
				String time=res.getString(3);
				String pl_path=res.getString(4);
				TupianBean tuBean=new TupianBean(sid, title, time, pl_path);
				list.add(tuBean);		
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			DBUtil.release(res, pre, con);
		}
		return list;
	}

	@Override
	public List<TupianBean> selectWodePhoto(int user_id,int pageNow) {
		List<TupianBean>list =new ArrayList<TupianBean>();
		int m = (pageNow - 1) * DataUtil.pageNum1;
		int n = DataUtil.pageNum1;
		String sql ="SELECT photo.`ph_id`,photo.`ph_name`,"
				+ "photo.`ph_date`, photo_library.`pl_path`"
				+ "FROM photo_library,photo WHERE "
				+ "photo.`ph_id`=photo_library.`ph_id`"
				+ "AND photo_library.`user_id`=photo.`user_id` "
				+ "AND photo.`user_id`=? AND "
				+ "photo.`pi_TopicID` IS NULL "
				+ "ORDER BY ph_date DESC limit ?,?";
		try {
			pre=con.prepareStatement(sql);
			pre.setInt(1, user_id);
			pre.setInt(2, m);
			pre.setInt(3, n);
			res=pre.executeQuery();
			while (res.next()) {
				int phid=res.getInt(1);
				String title=res.getString(2);
				String time=res.getString(3);
				String pl_path=res.getString(4);
				TupianBean tuBean=new TupianBean(phid, title, time, pl_path);
				list.add(tuBean);	
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			DBUtil.release(res, pre, con);
		}
		return list;
	}

	@Override
	public boolean insertPhoto(int userid,String phname,
			String date,boolean phhidden) {
		boolean falg=false;
		Connection con=DBUtil.getConnection();
		PreparedStatement pre=null;
		ResultSet res=null;
		String sql="insert into photo(user_id,ph_name,ph_date,ph_hidden)values (?,?,?,?);";
		try {
			pre=con.prepareStatement(sql);
			pre.setInt(1, userid);
			pre.setString(2, phname);
			pre.setString(3, date);
			pre.setBoolean(4, phhidden);
			pre.execute();
			falg=true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DBUtil.release(res, pre, con);
		}		
		return falg;
	}

}
